package com.css.fxfzdzzh.modules.eqLandslide.count.repository.impl;

import com.css.fxfzdzzh.modules.eqLandslide.count.dto.HazardousAreaDto;
import com.css.fxfzdzzh.modules.eqLandslide.count.entity.*;
import com.css.fxfzdzzh.modules.eqLandslide.count.param.LargeScreenParam;
import com.css.fxfzdzzh.modules.eqLandslide.count.param.TaskModelParam;
import com.css.fxfzdzzh.modules.eqLandslide.count.repository.EqLandslideRepository;
import com.css.fxfzdzzh.modules.eqLandslide.count.vo.*;
import com.css.fxfzdzzh.modules.evaluate.entity.CountAreaResult;
import com.css.fxfzdzzh.modules.tifData.service.ElevationMapService;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.util.PlatformSessionUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: fxfzdzzh
 * @Author: lhl
 * @CreateDate: 2023/2/15 13:58
 */
@Repository
public class EqLandslideRepositoryImpl implements EqLandslideRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    ElevationMapService elevationMapService;

    @Override
    public String getByGeom2(String geom, String geom1, Integer number1, Integer number2) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geom);
        String sql = "select st_astext(st_difference(";
        if (number2 == 3000 || number2 == 4000) {
            sql = sql + "(select st_geometryfromtext('" + geom + "',4490) as polygon), ";
        } else {
            sql = sql + "st_transform(st_buffer(st_transform(a.geom, " + epsg + ")," + number1 + "),4490), ";
        }
        sql = sql + "st_transform(st_buffer(st_transform(a.geom, " + epsg + ")," + number2 + "),4490))) as geom " +
                "from (select st_geometryfromtext('" + geom1 + "',4490) as geom) a";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public String getByGeom3(String geom, String geom1, Integer number1, Integer number2) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geom);
        String sql = "select st_astext(st_difference(";
        if (number2 == 2000) {
            sql = sql + "(select st_geometryfromtext('" + geom + "',4490) as polygon), ";
        } else {
            sql = sql + "st_transform(st_buffer(st_transform(a.geom, " + epsg + ")," + number1 + "),4490), ";
        }
        sql = sql + "st_transform(st_buffer(st_transform(a.geom, " + epsg + ")," + number2 + "),4490))) as geom " +
                "from (select st_geometryfromtext('" + geom1 + "',4490) as geom) a";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public int saveDzzhResultCcfx(DzzhResultCcfx entity) {
        String sql = "insert into dzzh_result_ccfx(id,pro_exceed,task_id,extends1" +
                ",model_id,file_path)" +
                "values(:id,:proExceed,:taskId,:extends1,:modelId,:filePath)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhResultLogisticGrowth(DzzhResultLogisticGrowth entity) {
        String sql = "insert into dzzh_result_logistic_growth(id,dz_intensity,task_id,extends1" +
                ",model_id,file_path,dp_flag)" +
                "values(:id,:dzIntensity,:taskId,:extends1,:modelId,:filePath,:dpFlag)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhResultExponential(DzzhResultExponential entity) {
        String sql = "insert into dzzh_result_exponential(id,dz_intensity,task_id,extends1" +
                ",model_id,file_path)" +
                "values(:id,:dzIntensity,:taskId,:extends1,:modelId,:filePath)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhResultLoessPlateau(DzzhResultLoessPlateau entity) {
        String sql = "insert into dzzh_result_loess_plateau(id,pro_exceed,task_id,extends1" +
                ",model_id,file_path)" +
                "values(:id,:proExceed,:taskId,:extends1,:modelId,:filePath)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhResultNewmark(DzzhResultNewmark entity) {
        String sql = "insert into dzzh_result_newmark(id,pro_exceed,task_id,extends1" +
                ",model_id,file_path)" +
                "values(:id,:proExceed,:taskId,:extends1,:modelId,:filePath)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public DzzhTaskModel getDzzhTaskModelByTaskIdAndResultStatus(String taskId, String resultStatus) {
        StringBuilder sql = new StringBuilder("SELECT * FROM dzzh_task_model where del_flag='0' and task_id = ?");
        if (PlatformObjectUtils.isNotEmpty(resultStatus)) {
            sql.append(" and result_status='" + resultStatus + "'");
        }
        sql = sql.append(" order by create_time");
        List<DzzhTaskModel> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhTaskModel.class), taskId);
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public int saveDzzhTaskModel(DzzhTaskModel entity) {
        String sql = "insert into dzzh_task_model(dtm_id,task_id,model_type,evaluate_status,result_status,evaluate_user" +
                ",evaluate_user_name,evaluate_time,create_user,create_time,update_user,update_time,del_flag,attach_id,lithology)" +
                "values(:dtmId,:taskId,:modelType,:evaluateStatus,:resultStatus,:evaluateUser,:evaluateUserName" +
                ",:evaluateTime,:createUser,:createTime,:updateUser,:updateTime,:delFlag,:attachId,:lithology)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhModelCcfx(DzzhModelCcfx entity) {
        String sql = "insert into dzzh_model_ccfx(id,task_id,slope_version,lithology_version,hdc_version" +
                ",road_version,a1_version,a2_version,a3_version,factor1,factor2,num1,num2,num3,num4,num5,num6,num7" +
                ",num8,num9,num10,num11,num12,num13,num14,num15,num16,num17,num18,num19,num20,dtm_id)" +
                "values(:id,:taskId,:slopeVersion,:lithologyVersion,:hdcVersion,:roadVersion,:a1Version,:a2Version" +
                ",:a3Version,:factor1,:factor2,:num1,:num2,:num3,:num4,:num5,:num6,:num7,:num8,:num9,:num10,:num11,:num12" +
                ",:num13,:num14,:num15,:num16,:num17,:num18,:num19,:num20,:dtmId)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhModelExponential(DzzhModelExponential entity) {
        String sql = "insert into dzzh_model_exponential(id,task_id,slope_version,dtm_id)" +
                "values(:id,:taskId,:slopeVersion,:dtmId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhModelLogisticGrowth(DzzhModelLogisticGrowth entity) {
        String sql = "insert into dzzh_model_logistic_growth(id,task_id,slope_version,dtm_id)" +
                "values(:id,:taskId,:slopeVersion,:dtmId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhModelLoessPlateau(DzzhModelLoessPlateau entity) {
        String sql = "insert into dzzh_model_loess_plateau(id,task_id,slope_version,lithology_version,phy_version" +
                ",climate_version,nat_den_version,a1_version,a2_version,a3_version,slope_num,lithology_num,phy_num" +
                ",climate_num,nat_den_num,a_num,dtm_id)" +
                "values(:id,:taskId,:slopeVersion,:lithologyVersion,:phyVersion,:climateVersion,:natDenVersion," +
                ":a1Version,:a2Version,:a3Version,:slopeNum,:lithologyNum,:phyNum,:climateNum,:natDenNum,:avNum,:dtmId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhModelNewmark(DzzhModelNewmark entity) {
        String sql = "insert into dzzh_model_newmark(id,task_id,slope_version,lithology_version,a_version,a1_version" +
                ",a2_version,a3_version,dtm_id,rock_soil_chroma,formula_number)" +
                "values(:id,:taskId,:slopeVersion,:lithologyVersion,:aVersion,:a1Version,:a2Version,:a3Version,:dtmId,:rockSoilChroma,:formulaNumber)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public List<DzzhTaskModel> getDzzhTaskModel(String taskId) {
        String sql = "SELECT * FROM dzzh_task_model where task_id = ? and del_flag = '0' and result_status='1'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhTaskModel.class), taskId);
    }

    @Override
    public DzzhTaskModel getDzzhTaskModelByDtmId(String dtmId) {
        String sql = "SELECT * FROM dzzh_task_model where dtm_id = ? and del_flag = '0'";
        List<DzzhTaskModel> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhTaskModel.class), dtmId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public Map<String, Object> getTaskModelVOPage(TaskModelParam param, int curPage, int pageSize) {
        String userId = PlatformSessionUtils.getUserId();
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t2.model_type modelType,COALESCE ( t2.evaluate_status, '1' ) evaluateStatus,t1.create_time createTime " +
                " FROM dzzh_task t1 LEFT JOIN dzzh_task_model t2 ON t1.dt_id = t2.task_id AND t2.del_flag = '0' and (result_status is null or result_status='2')" +
                " WHERE t1.del_flag = '0' and position('1' in disaster_type) > 0 and t1.create_user='" + userId + "' ";
        String countSql = "SELECT count(*) FROM dzzh_task t1 LEFT JOIN dzzh_task_model t2 ON t1.dt_id = t2.task_id AND t2.del_flag = '0' and (result_status is null or result_status='2')" +
                " WHERE t1.del_flag = '0' and position('1' in disaster_type) > 0 and t1.create_user='" + userId + "' ";
        String orderSql = " order by t1.create_time desc";
        if (PlatformObjectUtils.isNotEmpty(param.getTaskNum())) {
            sql = sql + " and t1.task_num='" + param.getTaskNum() + "'";
            countSql = countSql + " and t1.task_num='" + param.getTaskNum() + "'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getTaskName())) {
            sql = sql + " and t1.task_name='" + param.getTaskName()+ "'";
            countSql = countSql + " and t1.task_name='" + param.getTaskName()+ "'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
                    sql = sql + " and (t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + "-" + param.getArea() + "%'" +
                            " or t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + "、%' or t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + ",%' or t1.task_district = '" + param.getProvince() + "-" + param.getCity() +
                            "' or t1.task_district like '%" + param.getProvince() + "、%' or t1.task_district like '%" + param.getProvince() + ",%' or t1.task_district = '" + param.getProvince() + "')";
                    countSql = countSql + " and (t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + "-" + param.getArea() + "%'" +
                            "' or t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + "、%' or t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + ",%' or t1.task_district = '" + param.getProvince() + "-" + param.getCity() +
                            " or t1.task_district like '%" + param.getProvince() + "、%' or t1.task_district like '%" + param.getProvince() + ",%' or t1.task_district = '" + param.getProvince() + "')";
                } else {
                    sql = sql + " and (t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + "、%' or t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + ",%' or t1.task_district = '" + param.getProvince() + "-" + param.getCity() +
                            "' or t1.task_district like '%" + param.getProvince() + "、%' or t1.task_district like '%" + param.getProvince() + ",%' or t1.task_district = '" + param.getProvince() + "')";
                    countSql = countSql + " and (t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + "、%' or t1.task_district like '%" + param.getProvince() + "-" + param.getCity() + ",%'or t1.task_district = '" + param.getProvince() + "-" + param.getCity() +
                            "' or t1.task_district like '%" + param.getProvince() + "、%' or t1.task_district like '%" + param.getProvince() + ",%' or t1.task_district = '" + param.getProvince() + "')";
                }
            } else {
                sql = sql + " and (t1.task_district like '%" + param.getProvince() + "%'or t1.task_district = '" + param.getProvince() + "')";
                countSql = countSql + " and ( t1.task_district like '%" + param.getProvince() + "%'or t1.task_district = '" + param.getProvince() + "')";
            }

        }

        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        sql = sql + orderSql + " limit " + lastIndex + " offset " + firstIndex;

        List<TaskModelVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelVO.class));
        Integer total = jdbcTemplate.queryForObject(countSql, Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public TaskModelCcfxVO getTaskModelDetail1(String taskId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t2.lithology,t2.attach_id attachId,t3.*,t3,id modelId" +
                " FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_ccfx t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.result_status ='2' and t2.task_id=?";
        List<TaskModelCcfxVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelCcfxVO.class), taskId);
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelCcfxVO getTaskModelDetail1Mil(String taskId, String dtmId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.*" +
                " FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_ccfx t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.dtm_id = '" + dtmId + "' and t2.del_flag='0' and t2.result_status ='1' " +
                "and t2.task_id='" + taskId + "'";
        List<TaskModelCcfxVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelCcfxVO.class));
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelLoessPlateauVO getTaskModelDetail2(String taskId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version,t3.lithology_version,t3.phy_version,t3.climate_version,t3.nat_den_version,t3.a1_version,t3.a2_version,t3.a3_version,t3.slope_num,t3.lithology_num,t3.phy_num,t3.climate_num,t3.nat_den_num,t3.a_num,t3.id modelId" +
                " FROM dzzh_task t1" +
                " left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_loess_plateau t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.result_status ='2' and t2.task_id=?";
        List<TaskModelLoessPlateauVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelLoessPlateauVO.class), taskId);
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelLoessPlateauVO getTaskModelDetail2Mil(String taskId, String dtmId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version,t3.lithology_version,t3.phy_version,t3.climate_version,t3.nat_den_version,t3.a1_version,t3.a2_version,t3.a3_version,t3.slope_num,t3.lithology_num,t3.phy_num,t3.climate_num,t3.nat_den_num,t3.a_num" +
                " FROM dzzh_task t1" +
                " left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_loess_plateau t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.dtm_id = '" + dtmId + "' and t2.result_status ='1' " +
                "and t2.task_id='" + taskId + "'";
        List<TaskModelLoessPlateauVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelLoessPlateauVO.class));
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelExponentialVO getTaskModelDetail3(String taskId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version slopeVersion,t3.id modelId " +
                " FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_exponential t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.result_status ='2' and t2.task_id=?";
        List<TaskModelExponentialVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelExponentialVO.class), taskId);
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelExponentialVO getTaskModelDetail3Mil(String taskId, String dtmId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version slopeVersion" +
                " FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_exponential t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.dtm_id = '" + dtmId + "' and t2.result_status ='1' " +
                "and t2.task_id='" + taskId + "'";
        List<TaskModelExponentialVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelExponentialVO.class));
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelLogiGrowthVO getTaskModelDetail4(String taskId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version slopeVersion,t3.id modelId " +
                " FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_logistic_growth t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.result_status ='2' and t2.task_id=?";
        List<TaskModelLogiGrowthVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelLogiGrowthVO.class), taskId);
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelLogiGrowthVO getTaskModelDetail4Mil(String taskId, String dtmId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version slopeVersion" +
                " FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_logistic_growth t3 on t3.dtm_id like '%' || t2.dtm_id || '%' and t1.dt_id=t3.task_id " +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.dtm_id = '" + dtmId + "' and t2.result_status ='1' " +
                "and t2.task_id='" + taskId + "'";
        List<TaskModelLogiGrowthVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelLogiGrowthVO.class));
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelNewMarkVO getTaskModelDetail5(String taskId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district " +
                "taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t2.lithology,t2.attach_id attachId,t3.slope_version slopeVersion" +
                " ,t3.lithology_version lithologyVersion,t3.a1_version a1Version,t3.a2_version a2Version,t3.a3_version a3Version" +
                ",t3.rock_soil_chroma rockSoilChroma,t3.formula_number formulaNumber,t3.id modelId FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_newmark t3 on t3.dtm_id like '%'||t2.dtm_id||'%' and t1.dt_id=t3.task_id" +
                " where t1.del_flag='0' and t2.del_flag='0' and t2.result_status ='2' and t2.task_id=?";
        List<TaskModelNewMarkVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelNewMarkVO.class), taskId);
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public TaskModelNewMarkVO getTaskModelDetail5Mil(String taskId, String dtmId) {
        String sql = "SELECT t2.dtm_id dtmId,t1.dt_id taskId,t1.task_num taskNum,t1.task_name taskName,t1.task_district " +
                "taskDistrict,t1.disaster_type disasterType,t2.evaluate_user_name evaluateUserName,t3.slope_version slopeVersion" +
                " ,t3.lithology_version lithologyVersion,t3.a1_version a1Version,t3.a2_version a2Version,t3.a3_version a3Version" +
                ",t3.rock_soil_chroma rockSoilChroma,t3.formula_number formulaNumber FROM dzzh_task t1 left join dzzh_task_model t2 on t1.dt_id=t2.task_id" +
                " left join dzzh_model_newmark t3 on t3.dtm_id like '%'||t2.dtm_id||'%' and t1.dt_id=t3.task_id" +
                " where t1.del_flag='0' and t2.del_flag='0'  and t2.dtm_id = '" + dtmId + "' and t2.result_status ='1' " +
                "and t2.task_id='" + taskId + "'";
        List<TaskModelNewMarkVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelNewMarkVO.class));
        return query.size() > 0 ? query.get(0) : null;
    }

    @Override
    public void delDzzhTaskModel(String dtmId, String userId, Timestamp updateTime) {
        String sql = "update dzzh_task_model set del_flag='1',update_user='" + userId + "',update_time='" + updateTime +
                "' where dtm_id=?";
        jdbcTemplate.update(sql, dtmId);
    }

    @Override
    public void updateDzzhTaskModel(String dtmId, String evaluateStatus, String resultStatus, String userId,
                                    Timestamp updateTime) {
        String sql = "update dzzh_task_model set evaluate_status='" + evaluateStatus + "',result_status='" + resultStatus +
                "',update_user='" + userId + "',update_time" + "='" + updateTime + "' where dtm_id=?";
        jdbcTemplate.update(sql, dtmId);
    }

    @Override
    public void updateDzzhTaskModel1(DzzhTaskModel entity) {
        String sql = "update dzzh_task_model set task_id=:taskId,model_type=:modelType,evaluate_status=:evaluateStatus " +
                ",result_status=:resultStatus,evaluate_user=:evaluateUser,evaluate_user_name=:evaluateUserName" +
                ",evaluate_time=:evaluateTime,update_user=:updateUser,update_time=:updateTime,attach_id=:attachId,lithology=:lithology where dtm_id=:dtmId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void mergeGroupBatchSave(List<LithologyMergeResult> results) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(results.toArray());
        String sql = "insert into dzzh_lithology_merge_result(dlmr_id,task_id,lithology_version_code,lithology_id," +
                "geom,lithology_group_name,result_status,del_flag,create_user,create_time,operate_type,values1,flag)  values(" +
                ":dlmrId,:taskId,:lithologyVersionCode,:lithologyId,st_geometryfromtext(:geom,4490),:lithologyGroupName," +
                ":resultStatus,:delFlag,:createUser,:createTime,:operateType,:values1,:flag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<String> getLithologyGroupName(String taskId) {
        String sql = "SELECT lithology_group_name FROM dzzh_lithology_merge_result where task_id=? and del_flag='0' " +
                " group by lithology_group_name";
        return jdbcTemplate.queryForList(sql, String.class, taskId);
    }

    @Override
    public void delete(String taskId, String lithologyCode, String finalResultFlag) {
        String sql = "delete from dzzh_lithology_merge_result where del_flag='0' and task_id = '" + taskId + "' and " +
                "lithology_version_code='" + lithologyCode + "' and result_status='" + finalResultFlag + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void deleteByVersion(String taskId, String lithologyCode, String finalResultFlag) {
        String sql = "delete from dzzh_lithology_merge_result where del_flag='0' and task_id = '" + taskId + "' and " +
                "lithology_version_code!='" + lithologyCode + "' and result_status='" + finalResultFlag + "' and flag='2'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void saveDzzhResultNewmarkLithologyGroup(List<DzzhResultNewmarkLithologyGroup> lgList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(lgList.toArray());
        String sql = "insert into dzzh_result_newmark_lithology_group  values(" +
                ":drnlgId,:groupName,:cohesion,:viscosityAngle,:rockSoilWeight,:dtmId)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<LithologyGroup> getLithologyGroup(String dtmId) {
        String sql = "SELECT * FROM dzzh_result_newmark_lithology_group where  dtm_id=?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LithologyGroup.class), dtmId);
    }

    @Override
    public DzzhModelCcfx getModelDataByTaskId1(String taskId, String dtmId) {
        String sql = "SELECT * FROM dzzh_model_ccfx where task_id=? and dtm_id like '%" + dtmId + "%'";
        List<DzzhModelCcfx> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhModelCcfx.class), taskId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public DzzhModelLoessPlateau getModelDataByTaskId2(String taskId, String dtmId) {
        String sql = "SELECT * FROM dzzh_model_loess_plateau where task_id=? and dtm_id like '%" + dtmId + "%'";
        List<DzzhModelLoessPlateau> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhModelLoessPlateau.class), taskId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public DzzhModelExponential getModelDataByTaskId3(String taskId, String dtmId) {
        String sql = "SELECT * FROM dzzh_model_exponential where task_id=? and dtm_id like '%" + dtmId + "%'";
        List<DzzhModelExponential> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhModelExponential.class), taskId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public DzzhModelLogisticGrowth getModelDataByTaskId4(String taskId, String dtmId) {
        String sql = "SELECT * FROM dzzh_model_logistic_growth where task_id=? and dtm_id like '%" + dtmId + "%'";
        List<DzzhModelLogisticGrowth> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhModelLogisticGrowth.class), taskId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public DzzhModelNewmark getModelDataByTaskId5(String taskId, String dtmId) {
        String sql = "SELECT * FROM dzzh_model_newmark where task_id=? and dtm_id like '%" + dtmId + "%'";
        List<DzzhModelNewmark> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhModelNewmark.class), taskId);
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public List<DzzhResultCcfx> getResultDataByTaskId1(String taskId, String proExceed) {
        String sql = "SELECT t3.* FROM dzzh_task_model t1 " +
                " left join dzzh_model_ccfx t2 on t2.dtm_id like '%' || t1.dtm_id || '%' " +
                " left join dzzh_result_ccfx t3 on t3.model_id = t2.id " +
                " where t1.del_flag='0' and t1.result_status ='2' and t1.task_id=? ";
        if (PlatformObjectUtils.isNotEmpty(proExceed)) {
            sql = sql + " and t3.pro_exceed='" + proExceed + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhResultCcfx.class), taskId);
    }

    @Override
    public List<DzzhResultLoessPlateau> getResultDataByTaskId2(String taskId, String proExceed) {
        String sql = "SELECT t3.* FROM dzzh_task_model t1 " +
                " left join dzzh_model_loess_plateau t2 on t2.dtm_id like '%' || t1.dtm_id || '%' " +
                " left join dzzh_result_loess_plateau t3 on t3.model_id = t2.id " +
                " where t1.del_flag='0' and t1.result_status ='2' and t1.task_id=? ";
        if (PlatformObjectUtils.isNotEmpty(proExceed)) {
            sql = sql + " and t3.pro_exceed='" + proExceed + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhResultLoessPlateau.class), taskId);
    }

    @Override
    public List<DzzhResultExponential> getResultDataByTaskId3(String taskId, String dzIntensity) {
        String sql = "SELECT t3.* FROM dzzh_task_model t1 " +
                " left join dzzh_model_exponential t2 on t2.dtm_id like '%' || t1.dtm_id || '%' " +
                " left join dzzh_result_exponential t3 on t3.model_id = t2.id " +
                " where t1.result_status ='2' and t1.task_id=? and t1.del_flag='0' ";
        if (PlatformObjectUtils.isNotEmpty(dzIntensity)) {
            sql = sql + " and t3.dz_intensity='" + dzIntensity + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhResultExponential.class), taskId);
    }

    @Override
    public List<DzzhResultLogisticGrowth> getResultDataByTaskId4(String taskId, String dzIntensity) {
        String sql = "SELECT t3.* FROM dzzh_task_model t1 " +
                " left join dzzh_model_logistic_growth t2 on t2.dtm_id like '%' || t1.dtm_id || '%' " +
                " left join dzzh_result_logistic_growth t3 on t3.model_id = t2.id " +
                " where t1.result_status ='2' and t1.task_id=? and t1.del_flag='0'";
        if (PlatformObjectUtils.isNotEmpty(dzIntensity)) {
            sql = sql + " and t3.dz_intensity='" + dzIntensity + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhResultLogisticGrowth.class), taskId);
    }

    @Override
    public List<DzzhResultNewmark> getResultDataByTaskId5(String taskId, String proExceed) {
        String sql = "SELECT t3.* FROM dzzh_task_model t1 " +
                " left join dzzh_model_newmark t2 on t2.dtm_id like '%' || t1.dtm_id || '%' " +
                " left join dzzh_result_newmark t3 on t3.model_id = t2.id " +
                " where t1.del_flag='0' and t1.result_status ='2' and t1.task_id=? ";
        if (PlatformObjectUtils.isNotEmpty(proExceed)) {
            sql = sql + " and t3.pro_exceed='" + proExceed + "'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhResultNewmark.class), taskId);
    }

    @Override
    public int updateDtmIdByTaskId1(String newDtmId, String taskId, String oldDtmId) {
        String sql = "update dzzh_model_ccfx set dtm_id = '" + newDtmId + "' where task_id='" + taskId + "' and " +
                "dtm_id = '" + oldDtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int updateDtmIdByTaskId2(String newDtmId, String taskId, String oldDtmId) {
        String sql = "update dzzh_model_loess_plateau set dtm_id = '" + newDtmId + "' where task_id='" + taskId + "' and dtm_id = '" + oldDtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int updateDtmIdByTaskId3(String newDtmId, String taskId, String oldDtmId) {
        String sql = "update dzzh_model_exponential set dtm_id = '" + newDtmId + "' where task_id='" + taskId + "' and dtm_id = '" + oldDtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int updateDtmIdByTaskId4(String newDtmId, String taskId, String oldDtmId) {
        String sql = "update dzzh_model_logistic_growth set dtm_id = '" + newDtmId + "' where task_id='" + taskId + "' and dtm_id = '" + oldDtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int updateDtmIdByTaskId5(String newDtmId, String taskId, String oldDtmId) {
        String sql = "update dzzh_model_newmark set dtm_id = '" + newDtmId + "' where task_id='" + taskId + "' and dtm_id = '" + oldDtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhModelCcfx(String id) {
        String sql = "delete from dzzh_model_ccfx where id = '" + id + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhModelLoessPlateau(String id) {
        String sql = "delete from dzzh_model_loess_plateau where id = '" + id + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhModelExponential(String id) {
        String sql = "delete from dzzh_model_exponential where id = '" + id + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhModelLogisticGrowth(String id) {
        String sql = "delete from dzzh_model_logistic_growth where id = '" + id + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhModelNewmark(String id) {
        String sql = "delete from dzzh_model_newmark where id = '" + id + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhResultCcfx(String dtmId) {
        String sql = "delete from dzzh_result_ccfx where model_id = '" + dtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhResultLoessPlateau(String dtmId) {
        String sql = "delete from dzzh_result_loess_plateau where model_id = '" + dtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhResultExponential(String dtmId) {
        String sql = "delete from dzzh_result_exponential where model_id = '" + dtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhResultLogisticGrowth(String dtmId) {
        String sql = "delete from dzzh_result_logistic_growth where model_id = '" + dtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhResultNewmark(String dtmId) {
        String sql = "delete from dzzh_result_newmark where model_id = '" + dtmId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public int saveDzzhJcClimate(DzzhJcClimate entity) {
        String sql = "insert into dzzh_jc_climate(id,av_prec,geom,task_id,values1)" +
                "values(:id,:avPrec,st_geometryfromtext(:geomText,4490),:taskId,:values1)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public int delDzzhJcClimate(String taskId) {
        String sql = "delete from dzzh_jc_climate where task_id='" + taskId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int saveDzzhCountBuffer(DzzhCountBuffer entity) {
//        String sql = "insert into dzzh_count_buffer(id,values1,geom,task_id,type1,flag) " +
//                " values(:id,:values1,st_geometryfromtext(:geomText,4490),:taskId,:type,:flag)";
        String sql = "insert into dzzh_count_buffer(id,values1,geom,task_id,type1) " +
                " values(:id,:values1,st_geometryfromtext(:geomText,4490),:taskId,:type)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int saveDzzhCountBuffer1(DzzhCountBuffer entity, String tableName) {
        String sql = "insert into " + tableName + "(id,values1,geom,task_id)" +
                "values(:id,:values1,st_geometryfromtext(:geomText,4490),:taskId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public int delDzzhCountBuffer(String taskId, String type, String flag) {
        String sql = "delete from dzzh_count_buffer where task_id='" + taskId + "' and type1='" + type +
                "' and flag='" + flag + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int delDzzhCountBuffer1(String taskId, String tableName) {
        String sql = "delete from " + tableName + " where task_id='" + taskId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public List<String> getDataByDzIntensityOfDpzs(String dzIntensity) {
        String sql = "select t1.file_path from dzzh_result_logistic_growth t1 left join dzzh_model_logistic_growth t2 on t1.model_id=t2.id" +
                " left join (select * from dzzh_task_model where del_flag='0' and evaluate_status='2' and result_status='2' and model_type='4')t3 on t2.dtm_id like '%'||t3.dtm_id||'%'" +
                " left join (select * from dzzh_task where del_flag='0' and task_name like '%大屏展示%')t4 on t3.task_id=t4.dt_id" +
                " where t4.del_flag='0' and t3.del_flag='0' ";
        if (PlatformObjectUtils.isNotEmpty(dzIntensity)) {
            sql = sql + " and t1.dz_intensity='" + dzIntensity + "'";
        }
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public List<String> getByEstimateType(LargeScreenParam param) {
        String sql = "select dt_id from dzzh_task where del_flag='0' and disaster_type = '" + param.getEstimateType() + "' and task_name like '%大屏展示%'";
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            sql = sql + " and task_district like '%" + param.getProvince() + "%'";
        }
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public List<HazardousAreaDto> getHazardousArea(String ids, LargeScreenParam param) {
        StringBuilder sql = new StringBuilder("select t1.task_district,t2.* from dzzh_task t1 left join dzzh_count_area_result t2 on dt_id = t2.task_id where task_id in (" + ids + ") and dz_intensity = '" + param.getSecondaryScreening() + "'");
        if (param.getEstimateType() == 2) {
            sql.append(" and extend1 = '1'");
        }
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HazardousAreaDto.class));
    }

    @Override
    public void save(HistoricalSurfaceEntity entity) {
        jdbcTemplate.update("insert into dzzh_historical_surface  (id,province,region,longitude,latitude,length ,magnitude,time,geom)" +
                " values ('" + entity.getId() + "', '" + entity.getProvince() + "', '" + entity.getRegion() + "', '" + entity.getLongitude() + "', '" + entity.getLatitude() + "', '" + entity.getLength() + "', '" + entity.getMagnitude() + "', '" + entity.getTime() + "',st_geomfromtext('POINT(' || " + entity.getLongitude() + " || ' ' || " + entity.getLatitude() + " || ')', 4490)) ");
    }

    @Override
    public List<SurfaceRuptureZoneVo> getSurfaceRuptureZone() {
        String sql = "select province,city as region,longitude,latitude,earth_quake_level,rupture_length,earth_quake_time from dzzh_surface_rupture where source_from = '1' and del_flag = '0'\n" +
                "union all select province,region,longitude,latitude,magnitude as earth_quake_level,length as rupture_length,time as earth_quake_time from dzzh_historical_surface";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SurfaceRuptureZoneVo.class));
    }

    @Override
    public List<CountAreaResult> getStatisticsArea(String intensity,String type,String modelId,String modelType,String tableName) {
        String sql = "select * from "+tableName+" where dz_intensity='"+intensity+"' and model_id='"+modelId+"' and model_type='"+modelType+"'";
        if(!"".equals(type)){
            sql+=" and type='"+type+"' ";
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CountAreaResult.class));
    }
}
